Re: [SQL] Problems with default date 'now'

At 14:43 +0300 on 10/6/98, Petter Reinholdtsen wrote:


> Nope, that did not work.
>
> I get this reply from psql:
>
>   WARN:parser: parse error at or near "current_date"
>
> What is wrong.  Is this something new in PostgreSQL after v6.2.1?

Yes, it's something new. I still work with 6.2.1, so here's the deal:

Using a constant default value for a column causes the constant to be
evaluated once, at the creation of the table. That value is then kept with
the table schema, which means each row will be stamped with the same date.

In order to avoid that, you have to use a function as a default value.
Functions are evaluated each time a column is created. For this purpose, I
created an SQL function like this:

CREATE FUNCTION current_datetime() RETURNS datetime
AS 'SELECT ''now''::datetime'
LANGUAGE 'sql';

And I define the table as (in my case):

CREATE TABLE session
(
        session         int4
                        DEFAULT nextval( 'sess_no' )
                        NOT NULL,
        created         datetime
                        DEFAULT current_datetime() -- See here
                        NOT NULL,
        webuser         char(30)
);

You can define the function once, and use it for all the applications using
the same database.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma